﻿Imports System.Data.SqlClient
Public Class FormStudentEditor

    Public Sub LoadSex()
        Dim cn As New SqlConnection("server=.;uid=sa;pwd=123;database=SchoolDb;")
        cn.Open()

        Dim cmd As New SqlCommand("SELECT * FROM TBL_SEX;", cn)
        Dim dr = cmd.ExecuteReader()

        Dim dt As New DataTable()
        dt.Load(dr)
        dr.Close()

        cboSEX.DisplayMember = "SEX_NAME"
        cboSEX.ValueMember = "SEX_NAME"
        cboSEX.DataSource = dt
    End Sub
    Public Sub LoadProvince()
        Dim cn As New SqlConnection("server=.;uid=sa;pwd=123;database=SchoolDb;")
        cn.Open()

        Dim cmd As New SqlCommand("SELECT * FROM TBL_PROVINCE;", cn)
        Dim dr = cmd.ExecuteReader()

        Dim dt As New DataTable()
        dt.Load(dr)
        dr.Close()

        cboPROVINCE.DisplayMember = "PROVINCE_NAME"
        cboPROVINCE.ValueMember = "PROVINCE_CODE"
        cboPROVINCE.DataSource = dt
    End Sub
 
       
    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

        Dim sql As String
        If Me.txtCODE.Enabled = True Then
            sql = "INSERT INTO TBL_STUDENT VALUES(N'" & txtCODE.Text & "',N'" & txtNAME.Text & "',N'" & cboSEX.SelectedValue & "',N'" & dtpDATE.Value.ToString("yyyy-MM-dd") & "',N'" & cboPROVINCE.SelectedValue & "',N'" & txtPHONE.Text & "',N'" & txtAddress.Text & "');"
        Else
            sql = "UPDATE TBL_STUDENT SET NAME=N'" & txtNAME.Text & "',SEX=N'" & cboSEX.SelectedValue & "',BIRTH_DATE=N'" & dtpDATE.Value.ToString("yyyy-MM-dd") & "',PROVINCE_CODE=N'" & cboPROVINCE.SelectedValue & "',PHONE=N'" & txtPHONE.Text & "',ADDRESS=N'" & txtAddress.Text & "' WHERE STUDENT_CODE='" & txtCODE.Text & "';"
        End If
         
        Dim cn As New SqlConnection("server=.;uid=sa;pwd=123;database=SchoolDb;")
        cn.Open()
        Dim cmd As New SqlCommand(sql, cn)
        cmd.ExecuteNonQuery()
        Me.Close()
        FormStudent.RefreshData()

    End Sub

    Public Sub New()

        ' This call is required by the Windows Form Designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.
        LoadSex()
        LoadProvince()
    End Sub
End Class